package com.good.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * poi工具类
 * 
 * @author iii
 */
public class PoiUtils {
	private final static String PATH = "D:" + File.separator;

	/**
	 * 创建一个xls格式的文件
	 * 
	 * @param excelName
	 *            excel名字
	 * @param sheetName
	 *            sheet名字
	 */
	public static void createXLS(String excelName, String sheetName) {
		// 生成Workbook
		HSSFWorkbook wb = new HSSFWorkbook();
		// 添加Worksheet（不添加sheet时生成的xls文件打开时会报错）
		@SuppressWarnings("unused")
		Sheet sheet = wb.createSheet(sheetName);

		// 保存为Excel文件
		FileOutputStream out = null;

		try {
			out = new FileOutputStream(PATH + excelName + ".xls");
			wb.write(out);
		} catch (IOException e) {
			System.out.println(e.toString());
		} finally {
			try {
				if (out != null) {
					out.close();
				}
				out = null;
			} catch (IOException e) {
				System.out.println(e.toString());
			}
		}

	}

	/**
	 * 创建xlsx
	 * 
	 * @param excelName
	 *            文件名字
	 * @param sheetName
	 *            sheet名字
	 */
	public static void createXLSX(String excelName, String sheetName) {
		XSSFWorkbook wb = new XSSFWorkbook();
		wb.createSheet(sheetName);
		// 保存为Excel文件
		FileOutputStream out = null;
		try {
			out = new FileOutputStream(PATH + excelName + ".xlsx");
			wb.write(out);
		} catch (IOException e) {
			System.out.println(e.toString());
		} finally {
			try {
				if (out != null) {
					out.close();
				}
				out = null;
			} catch (IOException e) {
				System.out.println(e.toString());
			}
		}
	}

	public static void openXMS(String excelName) {

		// 方法一：使用WorkbookFactory
		FileInputStream in = null;
		Workbook wb = null;
		System.out.println(PATH);
		try {
			in = new FileInputStream(PATH + excelName);
			wb = WorkbookFactory.create(in);
		} catch (IOException e) {
			System.out.println(e.toString());
		} catch (InvalidFormatException e) {
			System.out.println(e.toString());
		} finally {
			try {
				if(in!=null){
					in.close();					
				}
				in=null;
			} catch (IOException e) {
				System.out.println(e.toString());
			}
		}

		System.out.println("====================Workbook====================");
		System.out.println("Number of Sheets:" + wb.getNumberOfSheets());
		System.out.println("Sheet3's name:" + wb.getSheetName(0));
		System.out.println();

		/*// 方法二：使用POIFSFileSystem
		try {
			in = new FileInputStream(PATH + excelName);
			POIFSFileSystem fs = new POIFSFileSystem(in);
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			System.out.println(e.toString());
		} finally {
			try {
				in.close();
			} catch (IOException e) {
				System.out.println(e.toString());
			}
		}

		System.out.println("====================Workbook====================");
		System.out.println("Number of Sheets:" + wb.getNumberOfSheets());
		System.out.println("Sheet3's name:" + wb.getSheetName(0));
		System.out.println();
*/
	}
	public void someMethod(){
		/**
		 * Sheet sheet = wb.createSheet("append sheet"); 追加sheet
		 * wb.cloneSheet(1);  克隆1sheet
		 * wb.setSheetName(i, "SheetName new");  修改sheet名字
		 * wb.removeSheetAt(1);  删除sheet
		 * wb.setFirstVisibleTab(2);设置下部Sheet名的Tab的第一个可见Tab（以左的Sheet看不见）
		 * wb.setSheetOrder("SheetName3", 1);wb.setSheetOrder(wb.getSheetName(4), 0);调整Sheet顺序
		 * //设置当前Sheet
		wb.setActiveSheet(wb.getNumberOfSheets() - 1);
		//（Excel的当前Sheet被设置，需要结合setSelected使用，不然下部Sheet名的Tab还是默认为第一个）
		//（需要选择多个Sheet的话，每个Sheet调用setSelected(true)即可）
		wb.getSheetAt(wb.getNumberOfSheets() - 1).setSelected(true);
		
		 * 
		 * wb.getSheet("SheetName4").createFreezePane(2, 2);固定窗口 
		 */
	}

	public static void main(String[] args) {
		// PoiUtils.createXLS();
		//PoiUtils.createXLSX("good", "soso");
		PoiUtils.openXMS("good.xlsx");
	}
}
